﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Text;

/// <summary>
///CBSJ 的摘要说明
/// </summary>
public partial class CBSJ
{
  public CBSJ()
  {
    //
    //TODO: 在此处添加构造函数逻辑
    //
  }

  /// <summary>
  /// //根据抄表id获取客户id
  /// </summary>
  /// <param name="sID">抄表id</param>
  /// <returns>客户id</returns>
  public static string GetSKHID(string sID)
  {
    string KHID = "";
    string sSql = "select * from SJLR_CBSJ where cbsjid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      dr.Read();
      KHID = dr["khid"].ToString();
    }
    dr.Close();
    return KHID;
  }

  /// <summary>
  /// //根据抄表id获取此行数据
  /// </summary>
  /// <param name="sID"></param>
  /// <returns></returns>
  public static SqlDataReader GetCBSJ(string sID)
  {
    string sSql = "select *,convert(varchar,cbsj,23) rq from SJLR_CBSJ where cbsjid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    return dr;
  }

  /// <summary>
  /// 获取当前抄表id和用气性质id下的气量明细
  /// </summary>
  /// <param name="sID">抄表id</param>
  /// <param name="sYQXZID">用气性质id</param>
  /// <returns></returns>
  public static DataTable GetBQQL(string sID, string sYQXZID)
  {
    string sSql = @"select a.khid,b.*,convert(varchar,a.cbsj,23) rq,c.yqxz from SJLR_CBSJ a,sjlr_bqql b,jcxx_yqxz c
  where a.cbsjid=b.cbsjid and a.cbsjid=@0 and b.yqxzid=@1 and b.yqxzid=c.yqxzid";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    cmdParams[1].Value = sYQXZID;
    DataTable dt = DbHelperSQL.Query(sSql, cmdParams).Tables[0];
    return dt;
  }
  public static DataTable Get_JGTZ_BQQL(string sID, string sYQXZID)
  {
    string sSql = @"select a.khid,b.*,convert(varchar,a.cbsj,23) rq,c.yqxz from SJLR_jgtz_CBSJ a,sjlr_jgtz_bqql b,jcxx_yqxz c
  where a.cbsjid=b.cbsjid and a.cbsjid=@0 and b.yqxzid=@1 and b.yqxzid=c.yqxzid";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    cmdParams[1].Value = sYQXZID;
    DataTable dt = DbHelperSQL.Query(sSql, cmdParams).Tables[0];
    return dt;
  }

  /// <summary>
  /// 此客户次日期前上次表数
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="RQ"></param>
  /// <returns></returns>
  public static string GetSCBS(string sKHID, DateTime RQ)
  {
    string sSql = @"select * from sjlr_cbsj a where a.khid=@0 and
  cbsj=(select max(cbsj) from sjlr_cbsj where khid=@0 and cbsj<@1 and cbsj<>(select rq  from s_tree))";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = RQ;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    string sSYBS = "0";
    if (dr.Read())
    {
      sSYBS = dr["bybs"].ToString();
    }
    dr.Close();
    return sSYBS;
  }

  /// <summary>
  ///  //上次抄表时间
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="RQ"></param>
  /// <returns></returns>
  public static string GetSCCBSJ(string sKHID, DateTime RQ)
  {
    string sSql = @"select max(cbsj) cbsj from sjlr_cbsj where khid=@0 and cbsj<@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = RQ;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    string sSYBS = "";
    if (dr.Read())
    {
      sSYBS = dr["cbsj"].ToString();
    }
    dr.Close();
    return sSYBS;
  }

  /// <summary>
  /// //判断此名称是否存在
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="sRQ"></param>
  /// <returns></returns>
  public static bool ExistID(string sKHID, string sRQ)
  {
    bool flag = false;
    string sSql = "select khid as num from sjlr_cbsj where convert(varchar(8),cbsj,112)=@0 and khid=@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sRQ;
    cmdParams[1].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      flag = true;
    }
    dr.Close();
    return flag;
  }

  /// <summary>
  /// //判断此日期之后是否已经录数
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="sRQ"></param>
  /// <returns></returns>
  public static bool ExistHQID(string sKHID, string sRQ)
  {
    bool flag = false;
    string sSql = "select cbsjid as num from sjlr_cbsj where cbsj>@0 and khid=@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sRQ;
    cmdParams[1].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      flag = true;
    }
    dr.Close();
    return flag;
  }

  /// <summary>
  /// //判断此日期之前是否有未上报数据
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="sRQ"></param>
  /// <returns></returns>
  public static bool ExistWSBSJ(string sKHID, string sRQ)
  {
    bool flag = false;
    string sSql = "select a.cbsjid as num from sjlr_cbsj a,sjlr_bqql b where cbsj<@0 and khid=@1 and a.cbsjid=b.cbsjid and bqql_sfsb=0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sRQ;
    cmdParams[1].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      flag = true;
    }
    dr.Close();
    return flag;
  }

  /// <summary>
  ///  //判断此日期之前是否有未审核数据
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="sRQ"></param>
  /// <returns></returns>
  public static bool ExistWSHSJ(string sKHID, string sRQ)
  {
    bool flag = false;
    string sSql = "select a.cbsjid as num from sjlr_cbsj a,sjlr_bqql b where cbsj<@0 and khid=@1 and a.cbsjid=b.cbsjid and bqql_sfsb=1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sRQ;
    cmdParams[1].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      flag = true;
    }
    dr.Close();
    return flag;
  }

  /// <summary>
  /// 此次抄表id后是否有新数据
  /// </summary>
  /// <param name="sCBSJID"></param>
  /// <returns></returns>
  public static bool ExistHQID(string sCBSJID)
  {
    bool flag = false;
    string sSql = "select cbsjid as num from sjlr_cbsj where khid=(select khid from sjlr_cbsj where cbsjid=@0) and cbsjid>@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sCBSJID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      flag = true;
    }
    dr.Close();
    return flag;
  }

  /// <summary>
  /// //应收合计
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="sRQ"></param>
  /// <returns></returns>
  public static double[] GetYS(string sKHID, string sRQ)
  {
    string sSql = @"select isnull(sum(bqql),0) ssql,isnull(sum(ysjg),0) ssjg,isnull(sum(ysjj),0) ssjj,isnull(sum(ysfy),0) ssfy
from sjlr_cbsj a,sjlr_bqql b where a.cbsjid=b.cbsjid and a.khid=@0 and convert(varchar(6),b.shsj,112)=@1 and bqql_sfsb=2";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    double[] d = new double[4] { 0, 0, 0, 0 };
    if (dr.Read())
    {
      d[0] = Convert.ToDouble(dr["ssql"].ToString());
      d[1] = Convert.ToDouble(dr["ssjg"].ToString());
      d[2] = Convert.ToDouble(dr["ssjj"].ToString());
      d[3] = Convert.ToDouble(dr["ssfy"].ToString());
    }
    dr.Close();
    return d;
  }

  /// <summary>
  /// 获取气量与实收费用//差异合计
  /// </summary>
  /// <param name="sKHID">客户id</param>
  /// <param name="sRQ">日期</param>
  /// <returns></returns>
  //  public static double[] GetCY(string sKHID, string sRQ)
  //  {
  //    string sSql = @"select isnull(sum(ssql),0) qlcy,isnull(sum(ssfy),0) fycy
  //from sjlr_cbsj a,sjlr_bqql b where a.cbsjid=b.cbsjid and a.khid=@0 and b.shsj<=@1 and ssfy<0 and bqql_sfsb=2";
  //    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
  //    cmdParams[0].Value = sKHID;
  //    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
  //    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
  //    double[] d = new double[2] { 0, 0 };
  //    if (dr.Read())
  //    {
  //      d[0] = Convert.ToDouble(dr["qlcy"].ToString());
  //      d[1] = Convert.ToDouble(dr["fycy"].ToString());
  //    }
  //    dr.Close();
  //    return d;
  //  }

  public static string GetLJCY(string sKHID, string sRQ)
  {
    string s = "0";
    string sSql = @"select (b.yjfy-a.ysfy) id from 
(select isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
WHERE a.cbsjid=b.cbsjid and b.khid=@0 and a.bqql_sfsb=2 and a.shsj<=@1) a,
(select isnull(sum(bqsj),0) yjfy from sjlr_jfsj a where khid=@0 and a.jfsj<=@1) b";
    SqlParameter[] cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      dr.Read();
      s = dr["id"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  ///  //是否撤回
  /// </summary>
  /// <param name="sID">抄表id</param>
  /// <returns></returns>
  public static bool SFCH(string sID)
  {
    bool flag = false;
    string sSql = @"select a.cbsjid from sjlr_cbsj a,sjlr_bqql b where a.cbsjid=b.cbsjid and a.cbsjid=@0 and ysfy+ssfy<>0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    double[] d = new double[2] { 0, 0 };
    if (dr.Read())
    {
      flag = true;
    }
    dr.Close();
    return flag;
  }

  /// <summary>
  /// 获取此客户本次结欠款  //本次结欠款
  /// </summary>
  /// <param name="sKHID"></param>
  /// <returns></returns>
  //  public static string GetBCJQK(string sKHID)
  //  {
  //    string s = "0";
  //    string sSql = @"select isnull(sum(ssfy),0) id from sjlr_bqql a,sjlr_cbsj b WHERE a.cbsjid=b.cbsjid and b.khid=@0 and ssfy<0
  // and a.bqql_sfsb=2";
  //    SqlParameter[] cmdParams = new SqlParameter[1] { new SqlParameter("@0", SqlDbType.VarChar, 20) };
  //    cmdParams[0].Value = sKHID;
  //    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
  //    if (dr.HasRows)
  //    {
  //      dr.Read();
  //      s = dr["id"].ToString();
  //    }
  //    dr.Close();
  //    return s;
  //  }

  /// <summary>
  /// 应收费用与已收费用差异
  /// </summary>
  /// <param name="sKHID"></param>
  /// <returns></returns>
  public static string GetFYCY(string sKHID)
  {
    string s = "0";
    string sSql = @"select (b.yjfy-a.ysfy) id from 
(select isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
WHERE a.cbsjid=b.cbsjid and b.khid=@0 and a.bqql_sfsb=2) a,
(select isnull(sum(bqsj),0) yjfy from sjlr_jfsj a where khid=@0) b";
    SqlParameter[] cmdParams = new SqlParameter[1] { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      dr.Read();
      s = dr["id"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 费用差异
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="jfsjid"></param>
  /// <returns></returns>
  public static string GetFYCY(string sKHID, string jfsjid)
  {
    string s = "0";
    string sSql = @"select (b.yjfy-a.ysfy) id from 
(select isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
WHERE a.cbsjid=b.cbsjid and b.khid=@0 and a.bqql_sfsb=2) a,
(select isnull(sum(bqsj),0) yjfy from sjlr_jfsj a where khid=@0 and jfsjid<>@1) b";
    SqlParameter[] cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = jfsjid;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      dr.Read();
      s = dr["id"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 根据客户id
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="sRQ"></param>
  /// <returns></returns>
  public static SqlDataReader GetSCCB(string sKHID, string sRQ)
  {
    string sSql = @"select a.*,b.cbr,convert(varchar,cbsj,23) rq from SJLR_CBSJ a,jcxx_cbr b
where a.cbrid=b.cbrid and KHID=@0 and cbsj=
(select max(cbsj) cbsj from sjlr_cbsj where khid=@0 and cbsj<=@1 and cbsj<>(select rq  from s_tree))";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sRQ;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    return dr;
  }

  /// <summary>
  /// /根据抄表id获取备注
  /// </summary>
  /// <param name="id"></param>
  /// <returns></returns>
  public static object GetNotes(string id)
  {
    string s = "";
    SqlDataReader dr = GetCBSJ(id);
    if (dr.Read())
    {
      s = dr["cbbz"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// //获取已有年份
  /// </summary>
  /// <returns></returns>
  public static DataSet GetYear()
  {
    string sSql = "select distinct datepart(yyyy,cbsj) year from sjlr_cbsj order by datepart(yyyy,cbsj) desc";
    DataSet ds = DbHelperSQL.Query(sSql);
    return ds;
  }

  /// <summary>
  /// 本年度本月份已计气量用户合计
  /// </summary>
  /// <param name="year"></param>
  /// <param name="month"></param>
  /// <returns></returns>
  public static string GetYJF(string year, string month)
  {
    string sSql = "select count(distinct khid) a from sjlr_cbsj where convert(varchar(7),cbsj,23)='" + year + "-" + month + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 本年度本月份已计气量合计
  /// </summary>
  /// <param name="year"></param>
  /// <param name="month"></param>
  /// <returns></returns>
  public static string GetYJQL(string year, string month)
  {
    string sSql = @"select sum(bqql) a from sjlr_cbsj a,sjlr_bqql b
 where a.cbsjid=b.cbsjid and bqql_sfsb=2 and convert(varchar(7),shsj,23)='" + year + "-" + month + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 本年度本月份已计气量，按照用气性质分组合计
  /// </summary>
  /// <param name="year"></param>
  /// <param name="month"></param>
  /// <returns></returns>
  public static DataTable GetYJQL_FZ(string year, string month)
  {
    string sSql = @"select yqxz,sum(bqql) a from sjlr_cbsj a,sjlr_bqql b,jcxx_yqxz c
 where a.cbsjid=b.cbsjid and bqql_sfsb=2 and b.yqxzid=c.yqxzid and convert(varchar(7),shsj,23)='" + year + "-" + month + "' group by yqxz";
    DataTable dt = DbHelperSQL.Query(sSql).Tables[0];
    return dt;
  }

  /// <summary>
  /// 本年度本月份应收费合计
  /// </summary>
  /// <param name="year"></param>
  /// <param name="month"></param>
  /// <returns></returns>
  public static string GetYSF(string year, string month)
  {
    string sSql = @"select sum(ysfy) a from sjlr_cbsj a,sjlr_bqql b
 where a.cbsjid=b.cbsjid and bqql_sfsb=2 and convert(varchar(7),shsj,23)='" + year + "-" + month + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 本年度，本月份新增气量合计
  /// </summary>
  /// <param name="year"></param>
  /// <param name="month"></param>
  /// <returns></returns>
  public static string GetXZQF(string year, string month)
  {
    string sSql = @"select sum(ssfy*-1) a from sjlr_cbsj a,sjlr_bqql b
 where a.cbsjid=b.cbsjid and bqql_sfsb=2 and ssfy<0 and convert(varchar(7),shsj,23)='" + year + "-" + month + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 本年度已计气量用户合计
  /// </summary>
  /// <param name="year"></param>
  /// <returns></returns>
  public static string GetYJF(string year)
  {
    string sSql = "select count(distinct khid) a from sjlr_cbsj where convert(varchar(4),cbsj,23)='" + year + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 本年度已计气量合计
  /// </summary>
  /// <param name="year"></param>
  /// <returns></returns>
  public static string GetYJQL(string year)
  {
    string sSql = @"select sum(bqql) a from sjlr_cbsj a,sjlr_bqql b
 where a.cbsjid=b.cbsjid and bqql_sfsb=2 and convert(varchar(4),shsj,23)='" + year + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 本年度已计气量按照用气性质分组
  /// </summary>
  /// <param name="year"></param>
  /// <returns></returns>
  public static DataTable GetYJQL_FZ(string year)
  {
    string sSql = @"select yqxz,sum(bqql) a from sjlr_cbsj a,sjlr_bqql b,jcxx_yqxz c
 where a.cbsjid=b.cbsjid and bqql_sfsb=2 and b.yqxzid=c.yqxzid and convert(varchar(4),shsj,23)='" + year + "' group by yqxz";
    DataTable dt = DbHelperSQL.Query(sSql).Tables[0];
    return dt;
  }

  /// <summary>
  /// 本年度应收气费合计
  /// </summary>
  /// <param name="year"></param>
  /// <returns></returns>
  public static string GetYSF(string year)
  {
    string sSql = @"select sum(ysfy) a from sjlr_cbsj a,sjlr_bqql b
 where a.cbsjid=b.cbsjid and bqql_sfsb=2 and convert(varchar(4),shsj,23)='" + year + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 本年度欠费合计
  /// </summary>
  /// <param name="year"></param>
  /// <returns></returns>
  public static string GetXZQF(string year)
  {
    string sSql = @"select sum(ssfy*-1) a from sjlr_cbsj a,sjlr_bqql b
 where a.cbsjid=b.cbsjid and bqql_sfsb=2 and ssfy<0 and convert(varchar(4),shsj,23)='" + year + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// //获取欠费用户列表
  /// </summary>
  /// <returns></returns>
  public static DataSet GetQFYH()
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append(@"select ROW_NUMBER() OVER (ORDER BY a.khid desc) AS xh,a.KHID,c.KHMC,c.KH,c.KHXX_SFSB,c.XXDZ,c.LXDH,a.ssfy BQFY,B.LJJE  
FROM
(select a.khid,(isnull(a.ysfy,0)-isnull(b.yjfy,0)) SSFY from 
  (select b.khid,isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
    WHERE a.cbsjid=b.cbsjid and a.bqql_sfsb=2 group by b.khid) a 
  left join 
  (select khid,isnull(sum(bqsj),0) yjfy from sjlr_jfsj a group by a.khid) b 
  on a.khid=b.khid
  where (isnull(a.ysfy,0)-isnull(b.yjfy,0))>0) a
left join jcxx_khxx c on a.khid=c.khid 
left join (select b.LJJE,b.KHID from 
  (SELECT KHID,MAX(yjid) rq  FROM [trqsfglxt].[dbo].[SJLR_YJLR] group by KHID) a,
    sjlr_yjlr b where a.KHID=b.KHID and a.rq=b.YJID) B
on a.KHID=B.KHID");
    //sbSql.Append("order by BYCBSJ desc");
    DataSet ds = DbHelperSQL.Query(sbSql.ToString());
    return ds;
  }

  /// <summary>
  /// 本年度抄表率，每个月的已抄表用户除以总抄表用户
  /// </summary>
  /// <param name="year"></param>
  /// <returns></returns>
  public static SqlDataReader GetCBL(string year)
  {
    string sSql = @"select a.yf,a.cb*100/(case when b.khs=0 then null else b.khs end) cbl from 
(select substring(convert(varchar(6),cbsj,112),5,2) yf,count(distinct a.khid) cb from sjlr_cbsj a,jcxx_khxx b 
where convert(varchar(4),cbsj,23)={0} and a.khid=b.khid and b.ic='否' group by substring(convert(varchar(6),cbsj,112),5,2)) a,
(select '01' yf,count(khid) khs from jcxx_khxx t where convert(varchar(6),rq,112)<={0}01 and ic='否' 
union all
select '02',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}02  and ic='否' 
union all
select '03',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}03  and ic='否' 
union all
select '04',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}04  and ic='否' 
union all
select '05',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}05  and ic='否' 
union all
select '06',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}06  and ic='否' 
union all
select '07',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}07  and ic='否' 
union all
select '08',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}08  and ic='否' 
union all
select '09',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}09  and ic='否' 
union all
select '10',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}10 and ic='否' 
union all
select '11',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}11 and ic='否' 
union all
select '12',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}12 and ic='否' ) b
where a.yf=b.yf";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(string.Format(sSql, year));
    return dr;
  }

  /// <summary>
  /// 本年度充卡率计算，每个月的ic卡户除以每个月的客户数量
  /// </summary>
  /// <param name="year"></param>
  /// <returns></returns>
  public static SqlDataReader GetCKL(string year)
  {
    string sSql = @"select a.yf,a.cb*100/(case when b.khs=0 then null else b.khs end) ckl from 
(select substring(convert(varchar(6),cbsj,112),5,2) yf,count(distinct a.khid) cb from sjlr_cbsj a,jcxx_khxx b 
where convert(varchar(4),cbsj,23)={0} and a.khid=b.khid and b.ic='是' group by substring(convert(varchar(6),cbsj,112),5,2)) a,
(select '01' yf,count(khid) khs from jcxx_khxx t where convert(varchar(6),rq,112)<={0}01 and ic='是' 
union all
select '02',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}02  and ic='是' 
union all
select '03',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}03  and ic='是' 
union all
select '04',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}04  and ic='是' 
union all
select '05',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}05  and ic='是' 
union all
select '06',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}06  and ic='是' 
union all
select '07',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}07  and ic='是' 
union all
select '08',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}08  and ic='是' 
union all
select '09',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}09  and ic='是' 
union all
select '10',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}10 and ic='是' 
union all
select '11',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}11 and ic='是' 
union all
select '12',count(khid) from jcxx_khxx t where convert(varchar(6),rq,112)<={0}12 and ic='是' ) b
where a.yf=b.yf";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(string.Format(sSql, year));
    return dr;
  }

  /// <summary>
  /// 当前月份下抄表用户数量，根据客户级别分组
  /// </summary>
  /// <param name="month"></param>
  /// <returns></returns>
  public static SqlDataReader GetCBYH(string year, string month)
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append("select khjb,ic,count(distinct c.khid) a from jcxx_khxx a,jcxx_khjb b,sjlr_cbsj c ");
    sbSql.Append("where a.khjbid=b.khjbid and a.khid=c.khid and a.ic='否' and convert(varchar(6),cbsj,112)='"
      + year + month + "' ");
    sbSql.Append("group by khjb,ic");
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sbSql.ToString());
    return dr;
  }

  /// <summary>
  /// 获取当前月份完成售气量
  /// </summary>
  /// <param name="month"></param>
  /// <returns></returns>
  public static SqlDataReader GetWCSQL(string year, string month)
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append("select khjb,ic,sum(ysql) a from jcxx_khxx a,jcxx_khjb b,sjlr_cbsj c ");
    sbSql.Append("where a.khjbid=b.khjbid and a.khid=c.khid and convert(varchar(6),cbsj,112)='"
      + year + month + "' ");
    sbSql.Append("group by khjb,ic");
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sbSql.ToString());
    return dr;
  }

  /// <summary>
  /// 获取未缴气费
  /// </summary>
  /// <param name="month">月份</param>
  /// <returns></returns>
  public static SqlDataReader GetWJQF(string year, string month)
  {
    StringBuilder sbSql = new StringBuilder();
    //sbSql.Append("select khjb,ic,sum(ssfy*-1) a from jcxx_khxx a,jcxx_khjb b,sjlr_cbsj c,sjlr_bqql d ");
    //sbSql.Append("where a.khjbid=b.khjbid and a.khid=c.khid and c.cbsjid=d.cbsjid and d.ssfy<0 and bqql_sfsb=2 and convert(varchar(6),cbsj,112)='"
    //  + year + month + "' ");
    //sbSql.Append("group by khjb,ic");
    sbSql.Append(@"select khjb,ic,sum(ssfy) a from 
jcxx_khxx a,jcxx_khjb b,(select a.khid,(isnull(a.ysfy,0)-isnull(b.yjfy,0)) SSFY from 
(select b.khid,isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
WHERE a.cbsjid=b.cbsjid and a.bqql_sfsb=2 and convert(varchar(6),cbsj,112)<='{0}' group by b.khid) a left join 
(select khid,isnull(sum(bqsj),0) yjfy from sjlr_jfsj a where convert(varchar(6),jfsj,112)<='{0}' group by a.khid) b
  on a.khid=b.khid
  where (isnull(a.ysfy,0)-isnull(b.yjfy,0))>0) c
  where a.KHJBID=b.KHJBID and a.khid=c.KHID
  group by KHJB,IC");
    string s = string.Format(sbSql.ToString(), year + month);
    SqlDataReader dr = DbHelperSQL.ExecuteReader(s);
    return dr;
  }
}
